using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Dynamic;
using System.Data.SqlClient;
using System.Configuration;
using Model;
using Common;

namespace DAL
{
    public class RawdataDAL
    {
        public static int Save(RawdataInfo info)
        {
            int id=0;
            int ids=0;
            if (info.UCode != null && info.UCode != "")
            { 
                string sqls = string.Format("select ID from [rawdata] where [UCode] ='{0}'",info.UCode);
                id = DataConvert.ToInt(CommonDAL.ExecuteScalar(sqls));
            }
            string sqlss = string.Format(@"select ID from [rawdata] where [Name] ='{0}' and [BLCode] ='{1}' and [Hospital] = '{2}'", info.Name,info.BLCode,info.Hospital);
            ids = DataConvert.ToInt(CommonDAL.ExecuteScalar(sqlss));
            if (ids > 0) {
                string sqlsss = string.Format(@"select ID from [rawdata] where [Name] ='{0}' and [BLCode] ='{1}' and [Hospital] = '{2}' and [UCode]='{3}'", info.Name, info.BLCode, info.Hospital,info.UCode);
                ids = DataConvert.ToInt(CommonDAL.ExecuteScalar(sqlsss));
            }
            if (id > 0 || ids>0)
            {
                return -1;
            }
            else
            {
                string sql = string.Format(@"insert into [rawdata]([Name],[UCode],[BLCode],[BPCount],[LKCount],[Hospital],[Remarks],[Founder],[ExperimentNumber],[Sex],[Age],[Opinions],[ReportDoctor],[TuWenShu],[DaTuWenShu],[EntryName],[Process],[State],[Late],[DelayedCause],[OutsourcingParty],[Termination],[TerminationReason],[Back],[BackReason],[Submitter],[Auditor],[ReferenceDoctor],[SpecimenSigningTime],[CreateDate]) 
                                            values(@Name,@UCode,@BLCode,@BPCount,@LKCount,@Hospital,@Remarks,@Founder,@ExperimentNumber,@Sex,@Age,@Opinions,@ReportDoctor,@TuWenShu,@DaTuWenShu,@EntryName,@Process,@State,@Late,@DelayedCause,@OutsourcingParty,@Termination,@TerminationReason,@Back,@BackReason,@Submitter,@Auditor,@ReferenceDoctor,@SpecimenSigningTime,@CreateDate);select @@identity;");
                SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@Name", info.Name),
                new SqlParameter("@UCode", info.UCode),
                new SqlParameter("@BLCode", info.ExperimentNumber),
                new SqlParameter("@BPCount", info.BPCount),
                new SqlParameter("@LKCount", info.LKCount),
                new SqlParameter("@Hospital", info.Hospital),
                new SqlParameter("@Remarks", info.Remarks),
                new SqlParameter("@Founder", info.Founder),
                new SqlParameter("@ExperimentNumber", info.ExperimentNumber),
                new SqlParameter("@Sex", info.Sex),
                new SqlParameter("@Age", info.Age),
                new SqlParameter("@Opinions", info.Opinions),
                new SqlParameter("@ReportDoctor", info.ReportDoctor),
                new SqlParameter("@TuWenShu", info.TuWenShu),
                new SqlParameter("@DaTuWenShu", info.DaTuWenShu),
                new SqlParameter("@EntryName", info.EntryName),
                new SqlParameter("@Process", info.Process),
                new SqlParameter("@State", info.State),
                new SqlParameter("@Late", info.Late),
                new SqlParameter("@DelayedCause", info.DelayedCause),
                new SqlParameter("@OutsourcingParty", info.OutsourcingParty),
                new SqlParameter("@Termination", info.Termination),
                new SqlParameter("@TerminationReason", info.TerminationReason),
                new SqlParameter("@Back", info.Back),
                new SqlParameter("@BackReason", info.BackReason),
                new SqlParameter("@Submitter", info.Submitter),
                new SqlParameter("@Auditor", info.Auditor),
                new SqlParameter("@ReferenceDoctor", info.ReferenceDoctor),
                new SqlParameter("@SpecimenSigningTime", info.SpecimenSigningTime),
                new SqlParameter("@CreateDate", info.CreateDate)
                };
                return DataConvert.ToInt(CommonDAL.ExecuteScalar(sql, paras));
            }            
        }
        public static int Update(RawdataInfo info)
        {
            string sql = string.Format("update [rawdata] set [Name]=@Name,[UCode]=@UCode,[BLCode]=@BLCode,[BPCount]=@BPCount,[LKCount]=@LKCount,[Hospital]=@Hospital,[Remarks]=@Remarks,[Founder]=@Founder,[ExperimentNumber]=@ExperimentNumber,[Sex]=@Sex,[Age]=@Age,[Opinions]=@Opinions,[ReportDoctor]=@ReportDoctor,[TuWenShu]=@TuWenShu,[DaTuWenShu]=@DaTuWenShu,[EntryName]=@EntryName,[Process]=@Process,[State]=@State,[Late]=@Late,[DelayedCause]=@DelayedCause,[OutsourcingParty]=@OutsourcingParty,[Termination]=@Termination,[TerminationReason]=@TerminationReason,[Back]=@Back,[BackReason]=@BackReason,[Submitter]=@Submitter,[Auditor]=@Auditor,[ReferenceDoctor]=@ReferenceDoctor,[SpecimenSigningTime]=@SpecimenSigningTime where [ID]=@ID");
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@Name", info.Name),
            new SqlParameter("@UCode", info.UCode),
            new SqlParameter("@BLCode", info.ExperimentNumber),
            new SqlParameter("@BPCount", info.BPCount),
            new SqlParameter("@LKCount", info.LKCount),
            new SqlParameter("@Hospital", info.Hospital),
            new SqlParameter("@Remarks", info.Remarks),
            new SqlParameter("@Founder", info.Founder),
            new SqlParameter("@ExperimentNumber", info.ExperimentNumber),
            new SqlParameter("@Sex", info.Sex),
            new SqlParameter("@Age", info.Age),
            new SqlParameter("@Opinions", info.Opinions),
            new SqlParameter("@ReportDoctor", info.ReportDoctor),
            new SqlParameter("@TuWenShu", info.TuWenShu),
            new SqlParameter("@DaTuWenShu", info.DaTuWenShu),
            new SqlParameter("@EntryName", info.EntryName),
            new SqlParameter("@Process", info.Process),
            new SqlParameter("@State", info.State),
            new SqlParameter("@Late", info.Late),
            new SqlParameter("@DelayedCause", info.DelayedCause),
            new SqlParameter("@OutsourcingParty", info.OutsourcingParty),
            new SqlParameter("@Termination", info.Termination),
            new SqlParameter("@TerminationReason", info.TerminationReason),
            new SqlParameter("@Back", info.Back),
            new SqlParameter("@BackReason", info.BackReason),
            new SqlParameter("@Submitter", info.Submitter),
            new SqlParameter("@Auditor", info.Auditor),
            new SqlParameter("@ReferenceDoctor", info.ReferenceDoctor),
            new SqlParameter("@SpecimenSigningTime", info.SpecimenSigningTime),
            new SqlParameter("@ID", info.ID)
            };
            return CommonDAL.ExecuteNoneQuery(sql, paras) ? info.ID : 0;
            
        }
        public static int UpdatePrints(int id, string prints)
        {
            string sql = string.Format("update [rawdata] set [Prints]='{0}' where [ID]={1}",prints,id);
            return CommonDAL.ExecuteNoneQuery(sql) ? id : 0;

        }
        public static RawdataInfo FindOneByID(int id)
        {
            DataRow dr = CommonDAL.FindOneByID(id, "[rawdata]");
            return Simple_Data(dr);
        }
        public static List<RawdataInfo> FindListByIDs(string ids)
        {
            DataTable dt = CommonDAL.FindListByIDs(ids, "[rawdata]");
            List<RawdataInfo> list = new List<RawdataInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        public static List<RawdataInfo> FindPage(string keyword, string ucode, string hospital, string experimentnumber, string begindate, string enddate, int startindex, int pagesize, string order, string desc, out int count)
        {
            StringBuilder filter = new StringBuilder();
            filter.AppendFormat(" and [rawdata].IsRuKu =0 ");
            
            if (B.MyAdmin != null) {
                AdminInfo userinfo = AdminDAL.FindOneByID(B.MyAdmin.ID);
                if (userinfo.UserName != "admin" && userinfo.DataQX != 1)
                {
                    filter.AppendFormat(" and [rawdata].Founder = '{0}'", userinfo.RealName);
                }
            }
            if (!string.IsNullOrEmpty(keyword))
            {
                filter.AppendFormat(" and [rawdata].Name like '%{0}%'", keyword);
            }
            if (!string.IsNullOrEmpty(ucode))
            {
                filter.AppendFormat(" and [rawdata].UCode like '%{0}%'", ucode);
            }
            if (!string.IsNullOrEmpty(hospital))
            {
                filter.AppendFormat(" and [rawdata].Hospital like '%{0}%'", hospital);
            }
            if (!string.IsNullOrEmpty(experimentnumber))
            {
                filter.AppendFormat(" and [rawdata].ExperimentNumber like '%{0}%'", experimentnumber);
            }
            if (!string.IsNullOrEmpty(begindate) && !string.IsNullOrEmpty(enddate))
            {
                filter.AppendFormat(" and [rawdata].SpecimenSigningTime between '{0}' and '{1}'", DataConvert.ToDateTime(begindate).ToString("yyyy-MM-dd 00:00:00"), DataConvert.ToDateTime(enddate).ToString("yyyy-MM-dd 23:59:59"));
            }
            DataTable dt = CommonDAL.FindPage(startindex, pagesize, order, desc, filter, "[rawdata]", out count);
            List<RawdataInfo> list = new List<RawdataInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        public static List<RawdataInfo> FindPageByRuKu(string keyword, string ucode, string hospital, string experimentnumber, string begindate, string enddate,int datatype, int startindex, int pagesize, string order, string desc, out int count)
        {
            StringBuilder filter = new StringBuilder();
            if (datatype != 0) 
            {
                filter.AppendFormat(" and [rawdata].DataType ={0} ",datatype);
            }

            filter.AppendFormat(" and [rawdata].IsRuKu =1 ");
            if (B.MyAdmin != null)
            {
                AdminInfo userinfo = AdminDAL.FindOneByID(B.MyAdmin.ID);
                if (userinfo.UserName != "admin" && userinfo.DataQX != 1)
                {
                    filter.AppendFormat(" and [rawdata].Founder = '{0}'", userinfo.RealName);
                }
            }
            if (!string.IsNullOrEmpty(keyword))
            {
                filter.AppendFormat(" and [rawdata].Name like '%{0}%'", keyword);
            }
            if (!string.IsNullOrEmpty(ucode))
            {
                filter.AppendFormat(" and [rawdata].UCode like '%{0}%'", ucode);
            }
            if (!string.IsNullOrEmpty(hospital))
            {
                filter.AppendFormat(" and [rawdata].Hospital like '%{0}%'", hospital);
            }
            if (!string.IsNullOrEmpty(experimentnumber))
            {
                filter.AppendFormat(" and [rawdata].ExperimentNumber like '%{0}%'", experimentnumber);
            }
            if (!string.IsNullOrEmpty(begindate) && !string.IsNullOrEmpty(enddate))
            {
                filter.AppendFormat(" and [rawdata].SpecimenSigningTime between '{0}' and '{1}'", DataConvert.ToDateTime(begindate).ToString("yyyy-MM-dd 00:00:00"), DataConvert.ToDateTime(enddate).ToString("yyyy-MM-dd 23:59:59"));
            }
            DataTable dt = CommonDAL.FindPage(startindex, pagesize, order, desc, filter, "[rawdata]", out count);
            List<RawdataInfo> list = new List<RawdataInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        public static List<RawdataInfo> FindAll()
        {
            DataTable dt = CommonDAL.GetDataTable(string.Format("select * from [rawdata]"));
            List<RawdataInfo> list = new List<RawdataInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        public static RawdataInfo FindByUCode(string ucode)
        {
            string sql = string.Format("select * from [rawdata] where UCode ='{0}'",ucode);
            DataRow dr = CommonDAL.GetDataRow(sql);
            return Simple_Data(dr);
        }

        public static RawdataInfo FindByUserInfo(string ucode, string name, string blcode, string hospital)
        {
            StringBuilder filter = new StringBuilder();
            filter.Append("select * from [rawdata] where 1=1");
            if (!string.IsNullOrEmpty(ucode))
            {
                filter.AppendFormat(" and [rawdata].UCode = '{0}'", ucode);
            }
            else 
            {
                if (!string.IsNullOrEmpty(name))
                {
                    filter.AppendFormat(" and [rawdata].Name = '{0}'", name);
                }
                if (!string.IsNullOrEmpty(blcode))
                {
                    filter.AppendFormat(" and [rawdata].BLCode = '{0}'", blcode);
                }
                if (!string.IsNullOrEmpty(hospital))
                {
                    filter.AppendFormat(" and [rawdata].Hospital = '{0}'", hospital);
                }
            }
            
            DataRow dr = CommonDAL.GetDataRow(filter.ToString());            
            return Simple_Data(dr);
        }
        
        private static RawdataInfo Simple_Data(DataRow dr)
        {
            RawdataInfo info = new RawdataInfo();
            if (dr != null)
            {
                info.ID = DataConvert.ToInt(dr["ID"]);
                info.Name = DataConvert.ToString(dr["Name"]);
                info.UCode = DataConvert.ToString(dr["UCode"]);
                info.BLCode = DataConvert.ToString(dr["BLCode"]);
                info.BPCount = DataConvert.ToInt(dr["BPCount"]);
                info.LKCount = DataConvert.ToInt(dr["LKCount"]);
                info.Hospital = DataConvert.ToString(dr["Hospital"]);
                info.Remarks = DataConvert.ToString(dr["Remarks"]);
                info.Founder = DataConvert.ToString(dr["Founder"]);
                info.ExperimentNumber = DataConvert.ToString(dr["ExperimentNumber"]);
                info.Sex = DataConvert.ToInt(dr["Sex"]);
                info.Age = DataConvert.ToInt(dr["Age"]);
                info.Opinions = DataConvert.ToString(dr["Opinions"]);
                info.ReportDoctor = DataConvert.ToString(dr["ReportDoctor"]);
                info.TuWenShu = DataConvert.ToInt(dr["TuWenShu"]);
                info.DaTuWenShu = DataConvert.ToInt(dr["DaTuWenShu"]);
                info.EntryName = DataConvert.ToString(dr["EntryName"]);
                info.Process = DataConvert.ToString(dr["Process"]);
                info.State = DataConvert.ToString(dr["State"]);
                info.Late = DataConvert.ToString(dr["Late"]);
                info.DelayedCause = DataConvert.ToString(dr["DelayedCause"]);
                info.OutsourcingParty = DataConvert.ToString(dr["OutsourcingParty"]);
                info.Termination = DataConvert.ToString(dr["Termination"]);
                info.TerminationReason = DataConvert.ToString(dr["TerminationReason"]);
                info.Back = DataConvert.ToString(dr["Back"]);
                info.BackReason = DataConvert.ToString(dr["BackReason"]);
                info.Submitter = DataConvert.ToString(dr["Submitter"]);
                info.Auditor = DataConvert.ToString(dr["Auditor"]);
                info.ReferenceDoctor = DataConvert.ToString(dr["ReferenceDoctor"]);
                info.SpecimenSigningTime = DataConvert.ToDateTime(dr["SpecimenSigningTime"]);                
                info.CreateDate = DataConvert.ToDateTime(dr["CreateDate"]);
                
            }
            return info;
        }

        public static RawdataInfo FindOneByIDs(int id)
        {
            DataRow dr = CommonDAL.FindOneByID(id, "[rawdata]");

             RawdataInfo info = new RawdataInfo();
             if (dr != null)
             {
                 info.prints = DataConvert.ToString(dr["Prints"]);
             }
            return info;
        }
    }
}

